In this blog I will show you how to export SQL Server table data in Excel sheet using c# code. Here I’m making application which import excels data in data table and export SQL Server data into excel sheet file.
Here I’ve two buttons; Import and Export which are using to import and export data from Excel to SQL Server and SQL Server to Excel.
Application Code:
private void btnImport_Click(object sender, EventArgs e)
{
// Create Data Table for MS-Office 2007 or 2003
System.Data.DataTable dtExcel = new System.Data.DataTable();
dtExcel.TableName = "MyExcelData";
string SourceConstr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='C:\Users\Sachindra\Desktop\MyExcel2003.xls';Extended Properties= 'Excel 8.0;HDR=Yes;IMEX=1'";
OleDbConnection con = new OleDbConnection(SourceConstr);
string query = "Select * from [Sheet1$]";
OleDbDataAdapter data = new OleDbDataAdapter(query, con);
data.Fill(dtExcel);
MessageBox.Show("Data Imported Successfully into DataTable");
}
private void btnExport_Click(object sender, EventArgs e)
{
// Create sql connection string
string conString = @"Data Source = XXXX ; Initial Catalog = XXXX; User Id = XXXX; Password = XXXX;";
SqlConnection sqlCon = new SqlConnection(conString);
sqlCon.Open();
SqlDataAdapter da = new SqlDataAdapter("select * from tblTest", sqlCon);
System.Data.DataTable dtMainSQLData = new System.Data.DataTable();
da.Fill(dtMainSQLData);
DataColumnCollection dcCollection = dtMainSQLData.Columns;
// Export Data into EXCEL Sheet
Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
ExcelApp.Application.Workbooks.Add(Type.Missing);
// ExcelApp.Cells.CopyFromRecordset(objRS);
for (int i = 1; i < dtMainSQLData.Rows.Count + 1; i++)
{
for (int j = 1; j < dtMainSQLData.Columns.Count + 1; j++)
{
if (i == 1)
ExcelApp.Cells[i, j] = dcCollection[j - 1].ToString();
else
ExcelApp.Cells[i, j] = dtMainSQLData.Rows[i - 1][j - 1].ToString();
}
}
ExcelApp.ActiveWorkbook.SaveCopyAs("C:\\Users\\Sachindra\\Desktop\\test.xls");
ExcelApp.ActiveWorkbook.Saved = true;
ExcelApp.Quit();
MessageBox.Show("Data Exported Successfully into Excel File");
}
}
So with the help of this application you could import data in SQL Server data table from Excel Sheet and export data from SQL Server table to Excel Sheet using C# code. Thanks for reading this article.
If you’re using any third party tool to perform this task or something like this task, there is one famous tool MindStick DataConverter which provides import export functionality. It is a free charge too so you can easily download it from here.
Anonymous User
05-Feb-2019Thanks for the help.
Dinesh Maurya
22-Jul-2016Pranay Bankar
13-Jul-2014for (int i = 1; i < dtMainSQLData.Rows.Count +2; i++)
{
for (int j = 1; j < dtMainSQLData.Columns.Count + 1; j++)
{
if (i == 1)
{
ExcelApp.Cells[i, j] = dcCollection[j - 1].ToString();
}
else
ExcelApp.Cells[i, j] = dtMainSQLData.Rows[i - 2][j - 1].ToString();
}
}
Animesh Datta
28-Mar-2014it is working if you modify the code by this
for (int i = 1; i < dtMainSQLData.Rows.Count +2; i++) //changes have done here ,
from dtMainSQLData.Rows.Count +1 to dtMainSQLData.Rows.Count +2
{
for (int j = 1; j < dtMainSQLData.Columns.Count + 1; j++)
{
if (i == 1)
{
ExcelApp.Cells[i, j] = dcCollection[j - 1].ToString();
}
else
ExcelApp.Cells[i, j] = dtMainSQLData.Rows[i - 2][j - 1].ToString();
}
}
Nibaal Bazzi
03-Dec-2013Hi, a really nice article.
I checked out a MindStick DataConverter tool which you mentioned and it really looks like a nice tool to have.
Also I came across this C# Excel component, it extremely simplified my code when I implemented this task. Here is how I achieved importing and exporting a DataTable to Excel in C#:
private void btnImport_Click(object sender, EventArgs e)
{
ExcelFile workbook = ExcelFile.Load("C:\\Users\\Sachindra\\Desktop\\MyExcel2003.xls");
ExcelWorksheet worksheet = workbook.Worksheets.ActiveWorksheet;
DataTable dtExcel = worksheet.CreateDataTable(new CreateDataTableOptions());
MessageBox.Show("Data Imported Successfully into DataTable");
}
private void btnExport_Click(object sender, EventArgs e)
{
DataTable dtMainSQLData = new DataTable();
// Fill DataTable ...
ExcelFile workbook = new ExcelFile();
ExcelWorksheet worksheet = workbook.Worksheets.Add(dtMainSQLData.TableName);
worksheet.InsertDataTable(dtMainSQLData, new InsertDataTableOptions());
workbook.Save("C:\\Users\\Sachindra\\Desktop\\test.xls");
MessageBox.Show("Data Exported Successfully into Excel File");
}
ravi bhushan
14-Mar-2013If i do so it show Header but always left First Row.